﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using VB = Microsoft.Vbe.Interop;
using System.Runtime.InteropServices;
using System.Reflection;

namespace AMOOF {

    public class Solver {

        private Dictionary<string, int> pdfOffsets;
        private int dataCount = 0;
        private Application app;
        private _Workbook workbook;
        private _Worksheet worksheet;
        private VB.VBComponent solverModule;
        private bool instantiated = false;

        private int pdfCount = 0;
        private int reportOffset = 20;


        private bool appVisible = true;

        public Solver() {
            if (!instantiated) {
                app = new Application();
                SnapInSolver(app);  //Snapping in the Excel Solver...
                app.Visible = appVisible;
                workbook = (_Workbook)app.Workbooks.Add(Missing.Value);
                worksheet = (_Worksheet)workbook.ActiveSheet;

                try {

                    workbook.Application.AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityLow;
                    
                    //This is a temporary fix that should be updated without string literals in it. -Zachary Haberman 8/4/2012
                    if(System.IO.File.Exists(@"C:\Program Files\Microsoft Office\Office14\Library\SOLVER\SOLVER.XLAM")) {
                        workbook.VBProject.References.AddFromFile(@"C:\Program Files\Microsoft Office\Office14\Library\SOLVER\SOLVER.XLAM");
                    }
                    else if (System.IO.File.Exists(@"C:\Program Files (x86)\Microsoft Office\Office14\Library\SOLVER\SOLVER.XLAM")) {
                        workbook.VBProject.References.AddFromFile(@"C:\Program Files (x86)\Microsoft Office\Office14\Library\SOLVER\SOLVER.XLAM");
                    }
                    else {
                        // throw new Exception("You must have Microsoft Office 2010 installed. If you feel you have reached this in error, please contact support.");
                    }
                    workbook.Activate();
                    solverModule = workbook.VBProject.VBComponents.Add(VB.vbext_ComponentType.vbext_ct_StdModule);

                    BuildTemplate();

                }
                catch (Exception ex) {
                    Program.log(ex.Message + "\r\n" + ex.StackTrace);
                    System.Windows.Forms.MessageBox.Show(ex.Message);
                    while (!SafelyRemove()) ;

                }

                instantiated = true;
            }

        }

        //Tested 6/12/2012 6:30PM - Zachary Haberman
        public void LoadData(Dictionary<double, int> data) {
            
            int counter = 3;
            dataCount = data.Count;
            foreach (var kvp in data) {
                
                worksheet.Cells[counter, 10].Value = kvp.Key;
                worksheet.Cells[counter++, 11].Value = kvp.Value;

            }
        }

        public IEnumerable<object> LoadData(Dictionary<double, int> data, int i) {

            int counter = 3;
            dataCount = data.Count;
            foreach (var kvp in data) {

                yield return (((double)counter) / data.Count) * 100.0;
                worksheet.Cells[counter, 10].Value = kvp.Key;
                worksheet.Cells[counter++, 11].Value = kvp.Value;

            }
        }

        public void LoadDataMoments(Dictionary<int, double> moments) {
            foreach (var kvp in moments) {
                if (kvp.Key < 0) {
                    worksheet.Cells[6 - kvp.Key, 13] = kvp.Value;
                }
                else {
                    worksheet.Cells[2 + kvp.Key, 13] = kvp.Value;
                }
            }
        }

        private void AddMomentFormula(ref Report r) {
            
            string moment = r.PDF.Moment;

            if (r.IsMixed) {
                moment = "<P1>*(" + moment + ")";
                string m2 = r.PDF.Moment;
                foreach (var kvp in r.PDF.Greeks) {
                    if (kvp.Key.ToString().Contains("2")) { continue; }
                    m2 = m2.Replace("<" + kvp.Key.ToString() + ">", "<" + kvp.Key.ToString() + "2>");
                }

                moment = moment + "+<P2>*(" + m2 + ")";
            }

            

            //Adds the moments titles E[X^k]...
            for (int i = 0; i < 4; i++) {
                worksheet.Cells[13 + (i) + (reportOffset * r.Index), 1].Value = "E[X^" + (i + 1) + "]";
                worksheet.Cells[13 + (i + 4) + (reportOffset * r.Index), 1].Value = "E[X^" + (-(i + 1)) + "]";
            }

            int index = 0;

            foreach (var kvp in r.PDF.Greeks) {
                moment = moment.Replace("<" + kvp.Key.ToString() + ">", "B$" + ((index + 3) + (r.Index * reportOffset)));
                index++;
            }

            

            moment = moment.Replace("<K>", "L3");

            //Just in case there is some inconsistency in the PDFs...
            if (!moment.Contains("=")) {
                moment = "=" + moment;
            }

            worksheet.Cells[13 + (r.Index * reportOffset), 2].Value = moment;

            worksheet.Range[worksheet.Cells[13 + (r.Index * reportOffset), 2], worksheet.Cells[13 + (r.Index * reportOffset), 2]].AutoFill(
                worksheet.Range[worksheet.Cells[13 + (r.Index * reportOffset), 2], worksheet.Cells[20 + (r.Index * reportOffset), 2]]
                );


        }

        public void SetSolverParams(ref Report r) {

            if (r.IsMixed) {
                int index = 0;
                foreach (var kvp in r.PDF.Greeks) {
                    if (kvp.Key.Equals(PDF.Greek.P1) || kvp.Key.Equals(PDF.Greek.P2)) { continue; } 
                    if(!r.Solved) {
                        worksheet.Cells[(index + 3) + (r.Index * reportOffset), 2].Value = 1.0;
                        index++;
                    }
                }
            }
            
            if (r.PDF.name == "Transformed Beta") {
                if (r.IsMixed) {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA] : 1.0;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA2] : 1.0;
                    worksheet.Cells[(2 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.TAU] : 1.0;
                    worksheet.Cells[(3 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.TAU2] : 1.0;
                    worksheet.Cells[(4 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 1.0;
                    worksheet.Cells[(5 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA2] : 1.0;
                    worksheet.Cells[(6 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.GAMMA] : 1.0;
                    worksheet.Cells[(7 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.GAMMA2] : 1.0;
                    worksheet.Cells[(8 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P1] : .5;
                    worksheet.Cells[(9 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P2] : .5;
                }
                else {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA] : 1.0;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.TAU] : 1;
                    worksheet.Cells[(2 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 7800;
                    worksheet.Cells[(3 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.GAMMA] : 22;
                }
            }
            else if (r.PDF.name == "Generalized Pareto") {
                if (r.IsMixed) {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 1.0;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA2] : 1.0;
                    worksheet.Cells[(2 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.GAMMA] : 1.0;
                    worksheet.Cells[(3 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.GAMMA2] : 1.0;
                    worksheet.Cells[(4 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P1] : .5;
                    worksheet.Cells[(5 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P2] : .5;
                }
                else {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 0.5;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.GAMMA] : 0.1;
                }
            }
            else if (r.PDF.name == "Burr") {
                if (r.IsMixed) {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA] : 1.0;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA2] : 1.0;
                    worksheet.Cells[(2 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 1.0;
                    worksheet.Cells[(3 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA2] : 1.0;
                    worksheet.Cells[(4 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.GAMMA] : 1.0;
                    worksheet.Cells[(5 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.GAMMA2] : 1.0;
                    worksheet.Cells[(6 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P1] : .5;
                    worksheet.Cells[(7 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P2] : .5;
                }
                else {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA] : 4;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 15;
                    worksheet.Cells[(2 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.GAMMA] : 6;    
                }
            }
            else if (r.PDF.name == "Inverse Burr") {
                if (r.IsMixed) {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.TAU] : 1.0;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.TAU2] : 1.0;
                    worksheet.Cells[(2 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.GAMMA] : 1.0;
                    worksheet.Cells[(3 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.GAMMA2] : 1.0;
                    worksheet.Cells[(4 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 1.0;
                    worksheet.Cells[(5 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA2] : 1.0;
                    worksheet.Cells[(6 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P1] : .5;
                    worksheet.Cells[(7 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P2] : .5;
                }
                else {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.TAU] : 1;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.GAMMA] : 5;
                    worksheet.Cells[(2 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 6;
                }
            }
            else if (r.PDF.name == "Pareto") {
                if (r.IsMixed) {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA] : 1.0;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA2] : 1.0;
                    worksheet.Cells[(2 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 1.0;
                    worksheet.Cells[(3 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA2] : 1.0;
                    worksheet.Cells[(4 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P1] : .5;
                    worksheet.Cells[(5 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P2] : .5;
                }
                else {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA] : 5;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 1000;
                }
            }
            else if (r.PDF.name == "Inverse Paralogistic") {
                if (r.IsMixed) {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 1.0;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA2] : 1.0;
                    worksheet.Cells[(2 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.TAU] : 1.0;
                    worksheet.Cells[(3 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.TAU2] : 1.0;
                    worksheet.Cells[(4 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P1] : .5;
                    worksheet.Cells[(5 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P2] : .5;
                }
                else {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 5000;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.TAU] : 5;  
                }         
            }
            else if (r.PDF.name == "Transformed Gamma") {
                if (r.IsMixed) {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA] : 1.0;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA2] : 1.0;
                    worksheet.Cells[(2 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 1.0;
                    worksheet.Cells[(3 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA2] : 1.0;
                    worksheet.Cells[(4 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.TAU] : 1.0;
                    worksheet.Cells[(5 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.TAU2] : 1.0;
                    worksheet.Cells[(6 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P1] : .5;
                    worksheet.Cells[(7 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P2] : .5;
                }
                else {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA] : 2;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 6000;
                    worksheet.Cells[(2 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.TAU] : 2;
                }
            }
            else if (r.PDF.name == "Inverse Transformed Gamma") {
                if (r.IsMixed) {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA] : 1.0;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA2] : 1.0;
                    worksheet.Cells[(2 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 1.0;
                    worksheet.Cells[(3 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA2] : 1.0;
                    worksheet.Cells[(4 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.TAU] : 1.0;
                    worksheet.Cells[(5 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.TAU2] : 1.0;
                    worksheet.Cells[(6 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P1] : .5;
                    worksheet.Cells[(7 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P2] : .5;
                }
                else {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA] : 3;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 7000;
                    worksheet.Cells[(2 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.TAU] : 5;
                }
            }
            else if (r.PDF.name == "Gamma") {
                if (r.IsMixed) {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA] : 1.0;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA2] : 1.0;
                    worksheet.Cells[(2 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 1.0;
                    worksheet.Cells[(3 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA2] : 1.0;
                    worksheet.Cells[(4 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P1] : .5;
                    worksheet.Cells[(5 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P2] : .5;
                }
                else {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA] : 6;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 0.9;
                }
            }
            else if (r.PDF.name == "Inverse Pareto") {
                if (r.IsMixed) {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.TAU] : 1.0;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.TAU2] : 1.0;
                    worksheet.Cells[(2 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 1.0;
                    worksheet.Cells[(3 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA2] : 1.0;
                    worksheet.Cells[(4 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P1] : .5;
                    worksheet.Cells[(5 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P2] : .5;
                }
                else {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.TAU] : 7;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 999;
                }
            }
            else if (r.PDF.name == "Loglogistic") {
                if (r.IsMixed) {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.GAMMA] : 1.0;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.GAMMA2] : 1.0;
                    worksheet.Cells[(2 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 1.0;
                    worksheet.Cells[(3 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA2] : 1.0;
                    worksheet.Cells[(4 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P1] : .5;
                    worksheet.Cells[(5 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P2] : .5;
                }
                else {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.GAMMA] : 5;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 1000;
                }
            }
            else if (r.PDF.name == "Paralogistic") {
                if (r.IsMixed) {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA] : 1.0;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA2] : 1.0;
                    worksheet.Cells[(2 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 1.0;
                    worksheet.Cells[(3 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA2] : 1.0;
                    worksheet.Cells[(4 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P1] : .5;
                    worksheet.Cells[(5 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P2] : .5;
                }
                else {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA] : 5;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 6666;
                }
            }
            else if (r.PDF.name == "Inverse Gamma") {
                if (r.IsMixed) {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA] : 1.0;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA2] : 1.0;
                    worksheet.Cells[(2 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 1.0;
                    worksheet.Cells[(3 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA2] : 1.0;
                    worksheet.Cells[(4 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P1] : .5;
                    worksheet.Cells[(5 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P2] : .5;
                }
                else {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA] : 5;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 8888;
                }
            }
            else if (r.PDF.name == "Weibull") {
                if (r.IsMixed) {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 1.0;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA2] : 1.0;
                    worksheet.Cells[(2 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.TAU] : 1.0;
                    worksheet.Cells[(3 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.TAU2] : 1.0;
                    worksheet.Cells[(4 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P1] : .5;
                    worksheet.Cells[(5 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P2] : .5;
                }
                else {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 6000;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.TAU] : 6;
                }
            }
            else if (r.PDF.name == "Inverse Weibull") {
                if (r.IsMixed) {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.TAU] : 1.0;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.TAU2] : 1.0;
                    worksheet.Cells[(2 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 1.0;
                    worksheet.Cells[(3 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA2] : 1.0;
                    worksheet.Cells[(4 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P1] : .5;
                    worksheet.Cells[(5 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P2] : .5;
                }
                else {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.TAU] : 6;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 7777;
                }
            }
            else if (r.PDF.name == "Exponential") {
                if (r.IsMixed) {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 1.0;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA2] : 1.0;
                    worksheet.Cells[(3 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P1] : .5;
                    worksheet.Cells[(4 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P2] : .5;
                }
                else {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 6000;
                }
            }
            else if (r.PDF.name == "Inverse Exponential") {
                if (r.IsMixed) {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 1.0;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA2] : 1.0;
                    worksheet.Cells[(2 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P1] : .5;
                    worksheet.Cells[(3 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P2] : .5;
                }
                else {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 5000;
                }
            }
            else if (r.PDF.name == "Lognormal") {
                if (r.IsMixed) {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.MU] : 1.0;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.MU2] : 1.0;
                    worksheet.Cells[(2 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.SIGMA] : 1.0;
                    worksheet.Cells[(3 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.SIGMA2] : 1.0;
                    worksheet.Cells[(4 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P1] : .5;
                    worksheet.Cells[(5 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P2] : .5;
                }
                else {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.MU] : 6;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.SIGMA] : 1;
                }
            }
            else if (r.PDF.name == "Inverse Gaussian") {
                if (r.IsMixed) {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.MU] : 1.0;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.MU2] : 1.0;
                    worksheet.Cells[(2 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 1.0;
                    worksheet.Cells[(3 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA2] : 1.0;
                    worksheet.Cells[(4 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P1] : .5;
                    worksheet.Cells[(5 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P2] : .5;
                }
                else {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.MU] : 5000;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 120;
                }
            }
            else if (r.PDF.name == "Single Parameter Pareto") {
                if (r.IsMixed) {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA] : 1.0;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA2] : 1.0;
                    worksheet.Cells[(2 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 1.0;
                    worksheet.Cells[(3 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA2] : 1.0;
                    worksheet.Cells[(4 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P1] : .5;
                    worksheet.Cells[(5 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P2] : .5;
                }
                else {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA] : 5;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 100;
                }
            }
            else if (r.PDF.name == "Generalized Beta") {
                if (r.IsMixed) {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA] : 1.0;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA2] : 1.0;
                    worksheet.Cells[(2 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.BETA] : 1.0;
                    worksheet.Cells[(3 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.BETA2] : 1.0;
                    worksheet.Cells[(4 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 1.0;
                    worksheet.Cells[(5 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA2] : 1.0;
                    worksheet.Cells[(6 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.TAU] : 1.0;
                    worksheet.Cells[(7 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.TAU2] : 1.0;
                    worksheet.Cells[(8 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P1] : .5;
                    worksheet.Cells[(9 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.P2] : .5;
                }
                else {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA] : 2;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.BETA] : 2;
                    worksheet.Cells[(2 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : dataCount + .1;
                    worksheet.Cells[(3 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.TAU] : 3;
                }
            }
            else if (r.PDF.name == "Beta") {
                if (r.IsMixed) {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA] : 1.0;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA2] : 1.0;
                    worksheet.Cells[(2 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.BETA] : 1.0;
                    worksheet.Cells[(3 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.BETA2] : 1.0;
                    worksheet.Cells[(4 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : 1.0;
                    worksheet.Cells[(5 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA2] : 1.0;
                }
                else {
                    worksheet.Cells[(0 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.ALPHA] : 5;
                    worksheet.Cells[(1 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.BETA] : 1;
                    worksheet.Cells[(2 + 3) + (r.Index * reportOffset), 2].Value = r.Solved ? r.PDF.Greeks[PDF.Greek.THETA] : dataCount + .1;
                }
            }

            app.Calculate();
            LoadPDFMoments(ref r);
        }

        private void AddGreeks(ref Report r) {

            SetSolverParams(ref r);

            //Add the Greeks
            //First parameter in Cells determines horizontal location.
            //Second parameter should always equal 1.

            int index = 0;

            foreach (var kvp in r.PDF.Greeks) {
                if (kvp.Key.Equals(PDF.Greek.P2)) {
                    worksheet.Cells[(index + 3) + (r.Index * reportOffset), 2].Value = "=1-B" + ((index + 2) + (r.Index * reportOffset));
                }
                if (kvp.Key.Equals(PDF.Greek.P1)) {
                    worksheet.Cells[(index + 3) + (r.Index * reportOffset), 2].Value = 0.5;
                }
                worksheet.Cells[(index + 3) + (r.Index * reportOffset), 1].Value = kvp.Key.ToString();
                index++;
            }

            

        } 

        private void AddPDFName(ref Report r) {
            string name = r.PDF.name;
            if (r.IsMixed) {
                name = "Mixed " + name;
            }
            //Add the PDF name
            worksheet.Range[worksheet.Cells[2 + (reportOffset * r.Index), 1], worksheet.Cells[2 + (reportOffset * r.Index), 9]].Merge();
            worksheet.Cells[2 + (reportOffset * r.Index), 1].Value = name;
            worksheet.Cells[2 + (reportOffset * r.Index), 1].Font.Bold = true;
            worksheet.Cells[2 + (reportOffset * r.Index), 1].Interior.Color = System.Drawing.Color.FromArgb(84, 138, 148).ToArgb();

        }

        private void AddAveragesAndLikelihood(ref Report r) {
            worksheet.Cells[12 + (r.Index * reportOffset), 3].Value = "Difference of Values";
            worksheet.Cells[12 + (r.Index * reportOffset), 3].Font.Bold = true;

            worksheet.Range[worksheet.Cells[21 + (r.Index * reportOffset), 1], worksheet.Cells[21 + (r.Index * reportOffset), 2]].Merge();
            worksheet.Cells[21 + (r.Index * reportOffset), 1].Value = "Average Difference";
            worksheet.Cells[21 + (r.Index * reportOffset), 1].Font.Bold = true;

            //worksheet.Cells[21 + (r.Index * reportOffset), 3].Value = "=SUM(C" + (13 + (r.Index * reportOffset)) + ":C" + (20 + (r.Index * reportOffset)) + ")/COUNT(C" + (13 + (r.Index * reportOffset)) + ":C" + (20 + (r.Index * reportOffset)) + ")";
            //worksheet.Cells[21 + (r.Index * reportOffset), 5].Value = "=SUM(D" + (13 + (r.Index * reportOffset)) + ":D" + (20 + (r.Index * reportOffset)) + ")/COUNT(D" + (13 + (r.Index * reportOffset)) + ":D" + (20 + (r.Index * reportOffset)) + ")";
            worksheet.Cells[21 + (r.Index * reportOffset), 3].Value = "=SUM(C" + (13 + (r.Index * reportOffset)) + ":C" + (20 + (r.Index * reportOffset)) + ")/COUNT(C" + (13 + (r.Index * reportOffset)) + ":C" + (20 + (r.Index * reportOffset)) + ")";
            worksheet.Cells[21 + (r.Index * reportOffset), 5].Value = "=SUM(D" + (13 + (r.Index * reportOffset)) + ":D" + (20 + (r.Index * reportOffset)) + ")/COUNT(D" + (13 + (r.Index * reportOffset)) + ":D" + (20 + (r.Index * reportOffset)) + ")";
            worksheet.Cells[21 + (r.Index * reportOffset), 4].Value = "Average Error";

            for (int i = 3; i < 11; i++) {
                worksheet.Cells[13 + (i - 3) + (r.Index * reportOffset), 3].Value = "=B" + (i + (r.Index * reportOffset) + 10) + "-M" + i;
                worksheet.Range[worksheet.Cells[13 + (i - 3) + (r.Index * reportOffset), 4], worksheet.Cells[13 + (i - 3) + (r.Index * reportOffset), 5]].Merge();
                worksheet.Cells[13 + (i - 3) + (r.Index * reportOffset), 4].Value = "=IFERROR((ABS((B" + (i + (r.Index * reportOffset) + 10) + "-M" + i + ")/M" + i + ")),\"\")";

            }

            //TODO: Make a more-sophisticated method to convert.... once you hit "AA"... you're screwed.s
            string c = getCell(14 + r.Index);
            worksheet.Cells[21 + (r.Index * reportOffset), 6].Value = "Likelihood";
            worksheet.Cells[21 + (r.Index * reportOffset), 7].Value = "=SUM(" + c + ":" + c + ")";

        }

        private void AddSolverConstraints(ref Report r) {
            
            //TODO: Add actual contraints...

            worksheet.Cells[12 + (r.Index * reportOffset), 8].Value = "MIN";
            worksheet.Cells[12 + (r.Index * reportOffset), 9].Value = "MAX";
            worksheet.Range[worksheet.Cells[13 + (r.Index * reportOffset), 8], worksheet.Cells[13 + (r.Index * reportOffset), 9]].Merge();
            worksheet.Cells[13 + (r.Index * reportOffset), 8].Value = "Constraints";

            for (int i = 3; i < 12; i++) {
                worksheet.Cells[i + (r.Index * reportOffset), 7].Value = "=IF(A" + (i + (r.Index * reportOffset)) + " = 0, \"\",A" + (i + (r.Index * reportOffset));
                if (worksheet.Cells[i + (r.Index * reportOffset), 7].Value != "") {
                    worksheet.Cells[i + (r.Index * reportOffset), 8].Value = .0001;
                }
                if (worksheet.Cells[i + (r.Index * reportOffset), 7].Value == "P1") {
                    worksheet.Cells[i + (r.Index * reportOffset), 9].Value = 1;
                }
            }
        
        }

        private string getCell(int intRep) {
            
            if(intRep < 1) {
                throw new ArgumentOutOfRangeException("You must provide an integer greater than zero!");
            }

            int count = 0;
            StringBuilder sb = new StringBuilder();

            while (intRep > 26) {
                count++;
                intRep -= 26;
            }

            if (count > 0) { sb.Append((char)(64 + count)); }
            sb.Append((char)(64 + intRep));

            return sb.ToString();
        }

        private void AddFunctionFormula(ref Report r) {
            //Setting up Formula
            worksheet.Cells[1, 14 + r.Index].Value = "=A" + (2 + (reportOffset * r.Index));
            worksheet.Cells[2, 14 + r.Index].Value = "Formula";

            string function = r.PDF.lnFunction;

            if (r.IsMixed) {
                function = function.Replace("*<FREQUENCY>", String.Empty);
                function = "<P1>*(" + function + ")";
                string f2 = r.PDF.lnFunction;
                f2 = f2.Replace("*<FREQUENCY>", String.Empty);
                foreach (var kvp in r.PDF.Greeks) {
                    if (kvp.Key.ToString().Contains("2")) { continue; }
                    
                    f2 = f2.Replace("<" + kvp.Key.ToString() + ">", "<" + kvp.Key.ToString() + "2>");
                }
                function = function + "+<P2>*(" + f2 + ")";
                function = "(" + function + ")*<FREQUENCY>";
            }


            int index = 0;

            foreach (var kvp in r.PDF.Greeks) {
                function = function.Replace("<" + kvp.Key.ToString() + ">", "B$" + ((index + 3) + (r.Index * reportOffset)));
                index++;
            }

            if (!function.Contains("=")) {
                function = "=" + function;
            }

            //char c = ASCIIEncoding.UTF8.GetChars(new byte[] { (byte)(14 + pdfCount + 64) })[0];
            function = function.Replace("<x>", "J3");
            function = function.Replace("<X>", "J3");

            function = function.Replace("<FREQUENCY>", "K3");

            worksheet.Cells[3, 14 + r.Index].Value = function;

            worksheet.Range[worksheet.Cells[3, 14 + r.Index], worksheet.Cells[3, 14 + r.Index]].AutoFill(worksheet.Range[worksheet.Cells[3, 14 + r.Index], worksheet.Cells[3 + dataCount - 1, 14 + r.Index]]);
        }

        //private string TestFunction(ref Report r) {
        //    string function = r.PDF.lnFunction;
        //    for (int i = 0; i < r.PDF.greeks.Count; i++) {
        //        function = function.Replace("<" + r.PDF.greeks[i].type.ToString() + ">", "B$" + ((i + 7) + (r.Index * reportOffset)));
        //    }
        //    if (function.Contains("=")) {
        //        function = function.Replace("=", String.Empty);
        //    }
        //    return function;
        //}

        private void AddStyle(ref Report r) {
            //Add a border to the PDF Report in Excel
            worksheet.Range[worksheet.Cells[2 + (reportOffset * r.Index), 1], worksheet.Cells[21 + (reportOffset * r.Index), 9]]
                .BorderAround(Type.Missing, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic, Type.Missing);
        }

        private void AddPDFOffsetLocation(string name, int offset) {
            int iOffset = 0;
            
            if(pdfOffsets == null) {
                pdfOffsets = new Dictionary<string,int>();
            }

            if(!pdfOffsets.TryGetValue(name, out iOffset)) {
                pdfOffsets.Add(name, offset);
            }
            else {
                throw new Exception(String.Format("Duplicate PDF found in collection! PDF: {0}", name));
            }

        }

        public Report BuildReport(Report r) {

            if (r.Solved) {
                return r;
            }
            else {
                //If the PDF is mixed, a Mixed prefix will be added to the name.
                string prefix = r.IsMixed ? "Mixed " : "";

                //Essentially a collection of pointer offsets...
                AddPDFOffsetLocation(prefix + r.PDF.name, pdfCount);
                r.Index = pdfCount;

                //Private method to add the PDF name to the spreadsheet
                AddPDFName(r: ref r);

                //Private method to add greeks to the spreadsheet
                AddGreeks(r: ref r);

                //Private method to add moments to the spreadsheet
                AddMomentFormula(r: ref r);

                //Private method to add likelihood, average error, and moments differences to the spreadsheet
                AddAveragesAndLikelihood(r: ref r);

                //Private method to add min and max constraints to the spreadsheet
                AddSolverConstraints(r: ref r);

                //Private method to add PDF formula to the spreadsheet
                AddFunctionFormula(r: ref r);

                //Private method to add Style formula to the spreadsheet
                AddStyle(r: ref r);




                pdfCount++;
                return r;
            }
        }

        public void RunSolver(Report r) {

            

            //If the PDF is mixed, a Mixed prefix will be added to the name.
            string prefix = r.IsMixed ? "Mixed " : "";

            int countOffset = pdfOffsets[prefix + r.PDF.name];

            int iterations = 32767;
            bool rerun = false;

            if (!r.Solved) {
                solverModule.CodeModule.AddFromString(BuildAverageErrorMacro(r.IsMixed? r.PDF.Greeks.Count-1: r.PDF.Greeks.Count, iterations, countOffset, -1));
                solverModule.CodeModule.AddFromString(BuildLikelihoodMacro(r.IsMixed ? r.PDF.Greeks.Count - 1 : r.PDF.Greeks.Count, iterations, countOffset, -1));
            }
            else {
                SetSolverParams(ref r);
            }

            app.Application.Run("SolverReset");
            try {
                app.Application.Run("SetSolverAveErrParams" + countOffset.ToString());
                //RunSolverForDifference(iterations, r.PDF.greeks.Count, ref r);
                RunSolverForDifference(iterations, r.PDF.Greeks.Count, ref r);

                app.Application.Run("SolverReset");
                app.Application.Run("SetSolverLikelihoodParams" + countOffset.ToString());
                //RunSolver(rerun, iterations, r.PDF.greeks.Count, ref r);
                RunSolver(rerun, iterations, r.PDF.Greeks.Count, ref r);
            }
            catch (Exception) {
                
                app.Application.Run("SolverFinish", 2);
                r.SolverResult = 22;
                r.Solved = true;
                LoadPDFGreeks(ref r);
                LoadPDFMoments(ref r);
            }
        }

        private void RunSolverForDifference(int iterations, int greekCount, ref Report r) {
            
                int val = app.Application.Run("SolverSolve", true);
                if (val == 9) { app.Application.Run("SolverFinish", 2); }
                else if (!(worksheet.Cells[21 + (r.Index * reportOffset), 7].Value > -2146826252.0)) {
                    app.Application.Run("SolverFinish", 2);
                }
                else { app.Application.Run("SolverFinish"); }
            
        }

        private void RunSolver(bool rerun, int iterations, int greekCount, ref Report r) {
            int value = app.Application.Run("SolverSolve", true);
            
            if (!(worksheet.Cells[21 + (r.Index * reportOffset), 7].Value > -2146826252.0)) {
                value = 12;
                app.Application.Run("SolverFinish", 2);
            }
            
            else if (!(worksheet.Cells[21 + (r.Index * reportOffset), 5].Value > -2146826252.0)) {
                object o = worksheet.Cells[21 + (r.Index * reportOffset), 5].Value;
                value = 21;
                app.Application.Run("SolverFinish", 2);
            }
            int count = 1;
            while (value != 0 && value != 1 && value != 2) {
                
                if (value == 3) {
                    app.Application.Run("SolverFinish");
                    //run again?
                    if (iterations != 32767) {
                        iterations++;
                    }
                    app.Application.Run("SolverOptions", iterations);
                    value = app.Application.Run("SolverSolve", true, "ShowTrial");
                }
                else if (value == 4 || value == 9) {
                    app.Application.Run("SolverFinish", 2);
                    if (rerun) {
                        break;
                    }
                    else {
                        app.Application.Run("SolverOptions", 10000, 2);
                        value = app.Application.Run("SolverSolve", true, "ShowTrial");
                        rerun = true;
                    }
                }
                else if (value == 6) {
                    app.Application.Run("SolverFinish");
                    break;
                }
                else if (value == 10) {
                    app.Application.Run("SolverFinish");
                    value = app.Application.Run("SolverSolve", true);
                }
                else if (value == 5) {
                    app.Application.Run("SolverFinish");


                    solverModule.CodeModule.AddFromString(BuildDifferenceMacro(greekCount, iterations, r.Index, count));
                    app.Application.Run("SolverReset");

                    app.Application.Run("SetSolverAveErrParams" + r.Index.ToString() + count.ToString());
                    count++;
                    value = app.Application.Run("SolverSolve", true);
                }
                else {
                    app.Application.Run("SolverFinish", 2);
                    break;
                }
            }

            app.Application.Run("SolverFinish");
            r.Solved = true;
            r.SolverResult = value;

            LoadPDFGreeks(ref r);
            LoadPDFMoments(ref r);

        }

        private void LoadPDFMoments(ref Report r) {
            object exists = 0;
            for (int i = 1; i < 5; i++) {
                if (r.PDF.Moments.TryGetValue(i, out exists)) {
                    r.PDF.Moments[i] = worksheet.Cells[(i + 12) + (r.Index * reportOffset), 2].Value;
                }
                else {
                    r.PDF.Moments.Add(i, worksheet.Cells[(i + 12) + (r.Index * reportOffset), 2].Value);
                }

                if (r.PDF.Moments.TryGetValue(-i, out exists)) {
                    r.PDF.Moments[-i] = worksheet.Cells[(i + 16) + (r.Index * reportOffset), 2].Value;
                }
                else {
                    r.PDF.Moments.Add(-i, worksheet.Cells[(i + 16) + (r.Index * reportOffset), 2].Value);
                }
            }

            r.PDF.AverageError = worksheet.Cells[21 + (r.Index * reportOffset), 5].Value;
        }

        private void LoadPDFGreeks(ref Report r) {
            for (int i = 0; i < r.PDF.Greeks.Count; i++) {
                r.PDF.Greeks[Reflector.GetEnum<PDF.Greek>(worksheet.Cells[3 + i + (r.Index * reportOffset), 1].Value)] = worksheet.Cells[(i + 3) + (r.Index * reportOffset), 2].Value;
            }
        }

        private string BuildDifferenceMacro(int greekCount, int iter, int count, int target = 0) {

            worksheet.Select();

            StringBuilder sb = new StringBuilder();
            if (target == -1) {
                sb.Append("Sub SetSolverDifferenceParams" + count + "()\n");
            }
            else 
                sb.Append("Sub SetSolverDifferenceParams" + count + target + "()\n");

            for (int i = 0; i < greekCount; i++) {
                if (worksheet.Cells[((count * reportOffset) + (i + 3)), 8].Value != null) {
                    sb.Append("    SolverAdd CellRef:=Range(\"$B$" + ((count * reportOffset) + (i + 3)) + "\"), Relation:=3, FormulaText:=\"$H$" + ((count * reportOffset) + (i + 3)) + "\"\n");
                }
                if (worksheet.Cells[((count * reportOffset) + (i + 3)), 9].Value != null) {
                    sb.Append("    SolverAdd CellRef:=Range(\"$B$" + ((count * reportOffset) + (i + 3)) + "\"), Relation:=1, FormulaText:=\"$I$" + ((count * reportOffset) + (i + 3)) + "\"\n");
                }
            }
            //Set the target cell...
            if (target > 8) { throw new Exception("The target cell value cannot be greater than 8"); }

            try {
                if (target == -1) target = 0;
                object o = worksheet.Cells[((count * reportOffset) + (13) + target), 3].Value;
                Type t = o.GetType();
                double d = worksheet.Cells[((count * reportOffset) + (13) + target), 3].Value;
                if (d > -2146826252.0) {
                    sb.Append("    SolverOk SetCell:= Range(\"$C$" + ((count * reportOffset) + (13) + target) + "\"), MaxMinVal:=3, ValueOf:=0, Engine:=1, ByChange:=Range(\"$B$" + ((count * reportOffset) + (3)) + ":$B$" + ((count * reportOffset) + (3 + greekCount - 1)) + "\")" + "\n");
                }
                else {
                    return null;
                }
            }
            catch (Exception e) {
                Program.log(e.Message + "\r\n" + e.StackTrace);

                return null;
            }
            

            sb.Append("    SolverOptions Iterations:=" + iter + ", MaxTime:=10000, IntTolerance:=1, SolveWithout:= False, PopulationSize:= 100" + "\n");
            sb.Append("    SolverOptions Derivatives:=2, Estimates:= 2, Scaling:=False, MultiStart:= False, Convergence:= 0.0001, SearchOption:= 2" + "\n");
            //sb.Append("    SolverSolve" + "\n");
            sb.Append("End Sub" + "\n"); 

            return sb.ToString();
        }

        private string BuildLikelihoodMacro(int greekCount, int iter, int count, int target = 0) {

            worksheet.Select();

            StringBuilder sb = new StringBuilder();
            if (target == -1) {
                sb.Append("Sub SetSolverLikelihoodParams" + count + "()\n");
            }
            else
                sb.Append("Sub SetSolverLikelihoodParams" + count + target + "()\n");

            for (int i = 0; i < greekCount; i++) {
                if (worksheet.Cells[((count * reportOffset) + (i + 3)), 8].Value != null) {
                    sb.Append("    SolverAdd CellRef:=Range(\"$B$" + ((count * reportOffset) + (i + 3)) + "\"), Relation:=3, FormulaText:=\"$H$" + ((count * reportOffset) + (i + 3)) + "\"\n");
                }
                if (worksheet.Cells[((count * reportOffset) + (i + 3)), 9].Value != null) {
                    sb.Append("    SolverAdd CellRef:=Range(\"$B$" + ((count * reportOffset) + (i + 3)) + "\"), Relation:=1, FormulaText:=\"$I$" + ((count * reportOffset) + (i + 3)) + "\"\n");
                }
            }
            //Set the target cell...
            if (target > 8) { throw new Exception("The target cell value cannot be greater than 8"); }

            try {
                if (target == -1) target = 0;
                object o = worksheet.Cells[((count * reportOffset) + (13) + target), 3].Value;
                Type t = o.GetType();
                double d = worksheet.Cells[((count * reportOffset) + (21)), 5].Value;
                if (d > -2146826252.0) {
                    sb.Append("    SolverOk SetCell:= Range(\"$G$" + ((count * reportOffset) + (21)) + "\"), MaxMinVal:=1, Engine:=1, ByChange:=Range(\"$B$" + ((count * reportOffset) + (3)) + ":$B$" + ((count * reportOffset) + (3 + greekCount - 1)) + "\")" + "\n");
                }
                else {
                    return null;
                }
            }
            catch (Exception e) {
                Program.log(e.Message + "\r\n" + e.StackTrace);
                System.Diagnostics.Debug.WriteLine(e.Message);

                return null;
            }


            sb.Append("    SolverOptions Iterations:=" + iter + ", MaxTime:=10000, IntTolerance:=1, SolveWithout:= False, PopulationSize:= 100" + "\n");
            sb.Append("    SolverOptions Derivatives:=2, Estimates:= 2, Scaling:=False, MultiStart:= False, Convergence:= 0.0001, SearchOption:= 2" + "\n");
            //sb.Append("    SolverSolve" + "\n");
            sb.Append("End Sub" + "\n");

            return sb.ToString();
        }

        private string BuildAverageErrorMacro(int greekCount, int iter, int count, int target = 0) {

            worksheet.Select();

            StringBuilder sb = new StringBuilder();
            if (target == -1) {
                sb.Append("Sub SetSolverAveErrParams" + count + "()\n");
            }
            else
                sb.Append("Sub SetSolverAveErrParams" + count + target + "()\n");

            for (int i = 0; i < greekCount; i++) {
                
                if (worksheet.Cells[((count * reportOffset) + (i + 3)), 8].Value != null) {
                    sb.Append("    SolverAdd CellRef:=Range(\"$B$" + ((count * reportOffset) + (i + 3)) + "\"), Relation:=3, FormulaText:=\"$H$" + ((count * reportOffset) + (i + 3)) + "\"\n");
                }
                if (worksheet.Cells[((count * reportOffset) + (i + 3)), 9].Value != null) {
                    sb.Append("    SolverAdd CellRef:=Range(\"$B$" + ((count * reportOffset) + (i + 3)) + "\"), Relation:=1, FormulaText:=\"$I$" + ((count * reportOffset) + (i + 3)) + "\"\n");
                }
            }
            //Set the target cell...
            if (target > 8) { throw new Exception("The target cell value cannot be greater than 8"); }

            try {
                if (target == -1) target = 0;
                object o = worksheet.Cells[((count * reportOffset) + (13) + target), 3].Value;
                Type t = o.GetType();
                double d = worksheet.Cells[((count * reportOffset) + (21)), 5].Value;
                if (d > -2146826252.0) {
                    sb.Append("    SolverOk SetCell:= Range(\"$E$" + ((count * reportOffset) + (21)) + "\"), MaxMinVal:=3, ValueOf:=0, Engine:=1, ByChange:=Range(\"$B$" + ((count * reportOffset) + (3)) + ":$B$" + ((count * reportOffset) + (3 + greekCount - 1)) + "\")" + "\n");
                }
                else {
                    return null;
                }
            }
            catch (Exception e) {
                Program.log(e.Message + "\r\n" +  e.StackTrace);

                return null;
            }


            sb.Append("    SolverOptions Iterations:=" + iter + ", MaxTime:=10000, IntTolerance:=1, SolveWithout:= False, PopulationSize:= 100" + "\n");
            sb.Append("    SolverOptions Derivatives:=2, Estimates:= 2, Scaling:=False, MultiStart:= False, Convergence:= 0.0001, SearchOption:= 2" + "\n");
            //sb.Append("    SolverSolve" + "\n");
            sb.Append("End Sub" + "\n");

            return sb.ToString();
        }
        

        private void BuildTemplate() {
            
            //Merges Cells
            worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, 9]].Merge();

            //Sets the value of a particular Cell
            worksheet.Cells[1, 1].Value = "PDF Collection";

            //Sets the cell text to Bold
            worksheet.Cells[1, 1].Font.Bold = true;

            //Sets the cell text alignment to Center
            worksheet.Cells[1, 1].Style.HorizontalAlignment = XlHAlign.xlHAlignCenter;

            //Setting up location for the data set...
            //These values will never change
            worksheet.Range[worksheet.Cells[1,10], worksheet.Cells[1,13]].Merge();
            worksheet.Cells[1,10].Value = "Data Set";
            worksheet.Cells[2, 10].Value = "Values";
            worksheet.Cells[2, 11].Value = "Frequency";
            worksheet.Range[worksheet.Cells[2, 12], worksheet.Cells[2, 13]].Merge();
            worksheet.Cells[2, 12].Value = "Moments";
            for (int i = 1; i < 5; i++) {
                worksheet.Cells[i + 2, 12].Value = i;
                worksheet.Cells[i + 6, 12].Value = -i;
            }
            /////////////////////////////////////////




        }

        public void ResetModule() {
            if (solverModule != null) { Marshal.ReleaseComObject(solverModule); }
            solverModule = null;
            solverModule = workbook.VBProject.VBComponents.Add(VB.vbext_ComponentType.vbext_ct_StdModule);

        }

        private void SnapInSolver(Application exApp) {
            foreach (AddIn ai in exApp.Application.AddIns) {

                //Console.WriteLine(ai.Name);
                if (ai.Name == "SOLVER.XLAM") {
                    ai.Installed = true;
                    Console.WriteLine("SOLVER.XLAM Successfully installed!");
                    break;
                }
            }
        }

        public bool SafelyRemove() {
            try {

                if (app != null) {
                    app.UserControl = false;
                    workbook.Close(false, null, null);
                    app.Workbooks.Close();
                    app.Quit();
                }

                if (solverModule != null) { Marshal.ReleaseComObject(solverModule); }
                if (worksheet != null) { Marshal.ReleaseComObject(worksheet); }
                if (workbook != null) { Marshal.ReleaseComObject(workbook); }
                if (app != null) { Marshal.ReleaseComObject(app); }


                app = null;
                workbook = null;
                worksheet = null;
                solverModule = null;
                GC.Collect();
                return true;

            }
            catch (Exception ex) {
                Program.log(ex.Message + "\r\n" +  ex.StackTrace);
                System.Windows.Forms.MessageBox.Show(ex.Message);
                return false;

            }

        }

    }
}
